package com.dbdata.move.dao;

import com.dbdata.move.model.*;
import com.dbdata.move.vo.UserInfo;
import com.wksc.framework.dao.BaseDao;
import org.hibernate.Criteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.ResultTransformer;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * TODO
 *
 * @author ChengJianLong
 */
@Repository
public class TaskDocDao extends BaseDao {
    /**
     * 查询任务书总数
     */
    public int countRWS() {
        String sql = "SELECT COUNT(t.RWSSN) " +
                     "FROM " +
                     "( " +
                     " SELECT r.RWSSN FROM rws r GROUP BY r.RWSSN " +
                     ") t";

        return Integer.parseInt(String.valueOf(this.getUniqueResultBySql(sql)));
    }

    /**
     * 查询记录单编号为空的记录单总数
     */
    public int countNullJLDSN() {
        String sql = "SELECT COUNT(t.FSSN) " +
                     "FROM " +
                     "( " +
                     " SELECT r.FSSN FROM jld r WHERE r.JLDSN IS NULL GROUP BY r.FSSN " +
                     ") t";

        return Integer.parseInt(String.valueOf(this.getUniqueResultBySql(sql)));
    }

    /**
     * 查询记录单编号不为空的记录单总数
     */
    public int countJLDSN() {
        String sql = "SELECT COUNT(t.JLDSN) " +
                     "FROM " +
                     "( " +
                     " SELECT r.JLDSN FROM jld r WHERE r.JLDSN IS NOT NULL GROUP BY r.JLDSN " +
                     ") t";

        return Integer.parseInt(String.valueOf(this.getUniqueResultBySql(sql)));
    }

    /**
     * 分页查询任务书编号
     *
     * @param start 记录开始位置
     * @param showNum 展示条数
     */
    public List<String> pagerRWSSN(int start, int showNum) {
        String sql = "SELECT r.RWSSN FROM rws r GROUP BY r.RWSSN ORDER BY r.ID";
        return this.findPagerDataBySql(sql, start, showNum);
    }

    /**
     * 分页查询记录单编号为空的记录单FSSN
     *
     * @param start 记录开始位置
     * @param showNum 展示条数
     */
    public List<Integer> pagerJldFSSN(int start, int showNum) {
        String sql = "SELECT r.FSSN FROM jld r WHERE r.JLDSN IS NULL GROUP BY r.FSSN ORDER BY r.ID";
        return this.findPagerDataBySql(sql, start, showNum);
    }

    /**
     * 分页查询记录单编号不为空的记录单JLDSN
     *
     * @param start 记录开始位置
     * @param showNum 展示条数
     */
    public List<String> pagerJldSN(int start, int showNum) {
        String sql = "SELECT r.JLDSN FROM jld r WHERE r.JLDSN IS NOT NULL GROUP BY r.JLDSN ORDER BY r.ID";
        return this.findPagerDataBySql(sql, start, showNum);
    }

    /**
     * 根据任务书编号查询任务书信息
     *
     * @param rwsSn 任务书编号
     */
    public List<Rws> listRwsByRwsSn(String rwsSn) {
        Criteria criteria = this.getCriteria(Rws.class);
        criteria.add(Restrictions.eq("rwssn", rwsSn));

        return this.list(criteria);
    }

    public Long getUserBaseInfoId(String trueName, String idCard, byte userType) {
        String sql = "SELECT ID FROM USER_BASE_INFO WHERE USER_NAME = ? AND IDENTITY_CARD = ? AND USER_TYPE = ?";

        Object obj = this.getUniqueResultBySql(sql, trueName, idCard, userType);

        return Long.parseLong(String.valueOf(obj));
    }

    public Airport getAirport(String icao) {
        Criteria criteria = this.getCriteria(Airport.class);

        criteria.add(Restrictions.eq("icao", icao));

        return this.getUniqueResult(criteria);
    }

    public List<Airport> listAirport() {
        Criteria criteria = this.getCriteria(Airport.class);
        return this.list(criteria);
    }

    public AirplaneNumber getAirplanNumber(String airplaneNumber, byte planeType) {
        Criteria criteria = this.getCriteria(AirplaneNumber.class);
        criteria.add(Restrictions.eq("planeNumber", airplaneNumber));
        criteria.add(Restrictions.eq("planeType", planeType));

        return this.getUniqueResult(criteria);
    }

    public List<AirplaneNumber> listAirplaneNumber(byte planeType) {
        Criteria criteria = this.getCriteria(AirplaneNumber.class);
        criteria.add(Restrictions.eq("planeType", planeType));
        return this.list(criteria);
    }

    public List<Jld> listJLDByFSSN(String jldSn) {
        Criteria criteria = this.getCriteria(Jld.class);
        criteria.add(Restrictions.eq("fssn", Integer.parseInt(jldSn)));

        return this.list(criteria);
    }

    public List<Jld> listJLDByJLDSN(String jldSn) {
        Criteria criteria = this.getCriteria(Jld.class);
        criteria.add(Restrictions.eq("jldsn", jldSn));

        return this.list(criteria);
    }

    public List<Ins> listIns() {
        Criteria criteria = this.getCriteria(Ins.class);
        return this.list(criteria);
    }

    public List<Stu> listStu() {
        Criteria criteria = this.getCriteria(Stu.class);
        return this.list(criteria);
    }

    public List<UserInfo> listUserInfo(byte userType) {
        String sql = "SELECT ID AS id, USER_NAME AS userName, IDENTITY_CARD AS identityCard FROM USER_BASE_INFO WHERE USER_TYPE = ? AND PASSPORT IS NULL";
        return this.listSql(sql, UserInfo.class, userType);
    }

    public List<UserInfo> listUserInfoForeign(byte userType) {
        String sql = "SELECT ID AS id, USER_NAME AS userName, PASSPORT AS identityCard FROM USER_BASE_INFO WHERE USER_TYPE = ? AND PASSPORT IS NOT NULL";
        return this.listSql(sql, UserInfo.class, userType);
    }

    public List<FlightCharacter> listFlightCharacter() {
        Criteria criteria = this.getCriteria(FlightCharacter.class);
        criteria.addOrder(Order.asc("flightCharacterSign"));
        return this.list(criteria);
    }
}
